﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
/*
	Todo:
	Adaptions for modify
	- refresh any views after modify,
		- too difficult to communicate. Instead detect error in SQL and remove duff fields from viewdef
	filter field types on modify. Disable controls with none.
	alt not possible if no pkey	
	
	Test cases
	- rename and shrink char field
		- if user says no to change the rename is still in effect.
	- rename and add default
	- rename and remove default

	
	More testing - more logic
	- can't alter fkey
	- can't alter computed
	- can't alter default?

exec sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
 

select s.name --default name
from sys.sysobjects s 
join sys.syscolumns c on s.parent_obj = c.id
where s.xtype = 'd'
and c.cdefault = s.id
and parent_obj= object_id('Foo')
and c.name ='id2'

*/
namespace SqlCeDriver
{
	public partial class ModifyColumnForm : Form
	{
		public ModifyColumnForm(String tableName, SqlTableSchema schema, DbView.FieldInfo fi)
		{
			InitializeComponent();
			IsAdding = (fi.Name.Length == 0);
			this.fieldInfo = fi;
			this.fieldInfoOriginal = fi.Clone() as DbView.FieldInfo;
			this.schema = schema;
			this.tableName = tableName;
			// load up the controls
			SetHeading(tableName);
			InitialiseTypeCategories();
			LoadControls();
		}

		public static DbView.FieldInfo NewField()
		{
			DbView.FieldInfo fi = new DbView.FieldInfo("", "", 0);
			return fi;
		}

		public String GenerateScript(bool isAltScriptWanted)
		{
			// bare alter table command. May be enough
			String script = String.Format("alter table {0} add {1}", this.tableName, FieldScripter.ScriptField(this.fieldInfo, this.tableName));
			// if adding the column it should be enough
			if (IsAdding) return script;

			// doesn't work with a constraint.
			// drop old name
			String df = FieldScripter.MakeDefaultName(this.fieldInfoOriginal, this.tableName);
			String dropConstraint = (df.Length == 0) ? "" :  String.Format("alter table {0} drop constraint {1};", Helper.SafeTableName(this.tableName), df);
			// add new if necessary
			df = FieldScripter.MakeDefaultName(this.fieldInfo, this.tableName);
			String addConstraint = (df.Length == 0) ? "" : String.Format("\r\nalter table {0} add constraint {1} default {2} for {3};",  
															Helper.SafeTableName(this.tableName), df, this.fieldInfo.MakeLiteral(this.fieldInfo.ConstraintDefinition), 
															Helper.SafeSqlField(this.fieldInfo.Name));
			// disable constraint for cor generation
			this.fieldInfo.HasDefault = false;
			// rename column if necessary *** WON'T WORK IN SQL CE
			//String rename = (this.m_FieldInfo.Name != this.m_FieldInfoOriginal.Name) ? String.Format("exec sp_RENAME '{0}.{1}', '{2}';\r\n", this.m_TableName, this.m_FieldInfoOriginal.Name, this.m_FieldInfo.Name) : "";
			String rename = "";
			if (this.fieldInfo.Name != this.fieldInfoOriginal.Name) 
				rename =  String.Format(ALT_MODIFY_COPY_DATA, this.tableName, this.fieldInfo.Name, this.fieldInfo.SqlType, this.fieldInfoOriginal.Name);

			script = dropConstraint + "\r\n" +
					 rename +"\r\n" +
		  			 String.Format("alter table {0} alter column {1};\r\n", Helper.SafeTableName(this.tableName), FieldScripter.ScriptField(this.fieldInfo, this.tableName)) +
					 addConstraint;

			if (df.Length > 0)
			{
				this.fieldInfo.HasDefault = true;
			}

			// 1st time in try the easy way
			if (!isAltScriptWanted)
				return script;

			// assemble data for the big template
			// 
			SqlScriptingHelper scriptHelper = new SqlScriptingHelper("tgt", this.schema);
			String keyCompare = scriptHelper.ScriptKeyCompare("h");
			String keyList =  scriptHelper.ScriptKeyList("");
			// bits 
			
			// hack because even if here therename part of the previous script has executed.
			// this is not good on so many levels. So in future it should rollback at which point
			// we need to replace below with the commented out version
			String oldName = this.fieldInfo.Name;
			
			String holdScript = String.Format(ALT_MODIFY_HOLDDATA, this.tableName, oldName, this.fieldInfo.Name, this.fieldInfo.SqlType, keyList);
			String holdScriptResize = String.Format(ALT_MODIFY_HOLDDATA_RESIZE, this.tableName, oldName, this.fieldInfo.Name, this.fieldInfo.SqlType, this.fieldInfo.Length, keyList);
			String changeColumnScript = String.Format(ALT_MODIFY_NOKEEPDATA, this.tableName, oldName, this.fieldInfo.SqlType,
													GenerateAddColumnScript(),
													dropConstraint,
													keyList,
													keyCompare);
			String restoreScript = String.Format(ALT_MODIFY_RESTORE_DATA, this.tableName, this.fieldInfo.Name, keyCompare);
			
			if (this.fieldInfo.IsCalc)
			{
				// if calc then data will be recalculated anyway
				script = changeColumnScript;
			}
			else
			{
				script = (this.fieldInfo.Type == "C" && (this.fieldInfoOriginal.Length > this.fieldInfo.Length)) ? holdScriptResize : holdScript;
				script += changeColumnScript + restoreScript;
			}
			return script;
		}
		// determine the effect of the alt script on the data
		// ie truncate/clear/move 
		public String AltEffect()
		{
			String effect = String.Format("Column {0} will be cleared of data and moved to the end of the column list", this.fieldInfo.Name);
			if (this.fieldInfoOriginal.Type == "C")
				if (this.fieldInfoOriginal.Length > this.fieldInfo.Length)
					effect = String.Format("Column {0} will be moved to the end of the column list and some data may be truncated", this.fieldInfo.Name);
			return effect;
		}

		private String GenerateAddColumnScript()
		{
			String script = String.Format("alter table {0} add {1}", this.tableName, FieldScripter.ScriptField(this.fieldInfo, this.tableName));
			return script;
		}

		private void ModifyColumnForm_Load(object sender, EventArgs e)
		{
			if (!IsAdding)
			{
				comboSqlType.Select();
				// freeze other types on modify
				RadioButton c = radioDate; c.Enabled = c.Checked;
				c = radioNumber; c.Enabled = c.Checked;
				c = radioOther; c.Enabled = c.Checked;
				//c = radioString; c.Enabled = c.Checked;
			}
		}
		
		private void BtnCancel_Click(object sender, EventArgs e)
		{
			this.DialogResult = System.Windows.Forms.DialogResult.Cancel;
			this.Close();
		}

		private void BtnSave_Click(object sender, EventArgs e)
		{
			// validate & transfer control info to fieldinfo
			SaveControls();
			if (ValidatesOk())
			{
				// a slow op will be a computed or default column with no-nulls
				if (!CheckCancelSlowOp())
				{
					this.DialogResult = System.Windows.Forms.DialogResult.OK;
					this.Close();
				}
			}
		}

		private void BtnScript_Click(object sender, EventArgs e)
		{
			SaveControls();
			if (ValidatesOk())
			{
				String script = GenerateScript(false);
				ShowSourceForm displayForm = new ShowSourceForm(this.fieldInfo.Name, script, "");
				displayForm.ShowDialog(this);
			}
		}

		private void Radio_CheckedChanged(object sender, EventArgs e)
		{
			RadioButton rb = sender as RadioButton;
			if (rb != null) 
				if (rb.Checked)
					SetFieldCombo(rb, 0);
		}

		private void ComboSqlType_SelectedIndexChanged(object sender, EventArgs e)
		{
			OnChangeType();
		}
		
		void OnChangeType()
		{
			bool enableLength = false;
			bool isDecimalSelected = LengthIsDp(comboSqlType.SelectedItem.ToString());
			if (radioString.Checked || isDecimalSelected) 
				enableLength = true;
			txtLength.Enabled = enableLength;
			lblLength.Enabled = enableLength;
			if (radioNumber.Checked)
				lblLength.Text = "Dp";
			else 
				lblLength.Text = "&Length";
		}

		////////////////////////////////////////////////////////////////////////////////////
		// real work
		//
		private void SetHeading(String tableName)
		{
			if (IsAdding)
				lblHeading.Text = String.Format("Add column to {0}", tableName);
			else
				lblHeading.Text = String.Format("Modify column {1} in {0}", tableName, this.fieldInfo.Name);
		}

		public bool IsAdding {get; set; }
		
		void LoadControls()
		{
			txtName.Text = this.fieldInfo.Name;
			//txtComputed.Text = (this.m_FieldInfo.IsCalc) ? this.m_FieldInfo.ConstraintDefinition : "";
			txtDefault.Text = (this.fieldInfo.HasDefault) ? this.fieldInfo.ConstraintDefinition : "";
			int selection;
			RadioButton control = SetRadioSelectionFromFieldInfo(out selection);
			control.Checked = true;
			SetFieldCombo(control, selection);
			comboNulls.Items.Add("Yes");
			comboNulls.Items.Add("No");
			comboNulls.SelectedIndex = (this.fieldInfo.AllowsNulls) ? 0 : 1;
			if (radioString.Checked)
				txtLength.Text = (this.fieldInfo.Length == -1) ? "MAX" : 
								 (this.fieldInfo.Length == 0) ? "":
								this.fieldInfo.Length.ToString();
			else
				if (LengthIsDp(this.fieldInfo.SqlType))
					txtLength.Text = this.fieldInfo.Dp.ToString();
			if (!IsAdding)
			{
				//comboOnTruncate.SelectedIndex = 0;
				//comboOnError.SelectedIndex = 0;
				// can't rename 
				// you can? txtName.Enabled = false;
				comboSqlType.Focus();
			}
			else // disable options
			{
				//groupModOptions.Enabled = false;
				//checkClearData.Enabled = false;
				//lblOnError.Enabled = false;
				//comboOnTruncate.Enabled = false;
				//lblOnTruncate.Enabled = false;
				//comboOnError.Enabled = false;
			}
		}

		void SaveControls()
		{
			this.fieldInfo.Name = txtName.Text;
			this.fieldInfo.HasDefault = false;
			this.fieldInfo.IsCalc = false;
			if (txtDefault.Text.Length > 0)
			{
				this.fieldInfo.ConstraintDefinition = txtDefault.Text;
				this.fieldInfo.HasDefault = true;
			}
			this.fieldInfo.SqlType = comboSqlType.SelectedItem.ToString();
			this.fieldInfo.AllowsNulls = (comboNulls.SelectedIndex == 1) ? false : true;
			if (txtLength.Text.ToUpper() == "MAX")
			{
				this.fieldInfo.Length = -1;
			}
			else
			{
				int fieldLength;
				if (LengthIsDp(this.fieldInfo.SqlType))
				//if (this.m_FieldInfo.SqlType == SQL_DECIMAL_TYPE)
				{
					if (int.TryParse(txtLength.Text, out fieldLength) == false)
						this.fieldInfo.Dp =  0;
					else
						this.fieldInfo.Dp =  fieldLength;
					if (IsAdding)  // otherwise keep the existing length
						this.fieldInfo.Length = DEFAULT_DECIMAL_LENGTH; 
				}
				else
				{
					if (int.TryParse(txtLength.Text, out fieldLength) == false)
						this.fieldInfo.Length = INVALID_FIELD_LENGTH;
					else
						this.fieldInfo.Length = fieldLength;
				}
			}
		}

		bool ValidatesOk()
		{
			if (txtName.Text.Trim().Length == 0)
				return ValidationError("You forgot to enter a name for the new column!", txtName);

			bool lenReqd = (this.fieldInfo.SqlType.ToUpper().IndexOf("CHAR") != -1);
			if (lenReqd)
				if (this.fieldInfo.Length == INVALID_FIELD_LENGTH)
					return ValidationError("String types need a valid length", txtLength);

			return true;
		}

		private bool ValidationError(string message, Control focusControl)
		{
			MessageBox.Show(message, "Incorrect entry", MessageBoxButtons.OK, MessageBoxIcon.Error);
			focusControl.Focus();
			return false; // ie fails validation
		}

		private bool CheckCancelSlowOp()
		{
			if (!this.fieldInfo.HasDefault || this.fieldInfo.AllowsNulls)
				return false; // not slow 
			String warningReason = "Column has a default value and does not allow nulls";
			// potential slow operation
			String op = (IsAdding) ? "Add" : "Modify";
			String warning = String.Format(
@"{1}ing this field will involve updating all previous rows. 
The reason is: [ {0} ]
For a large table this may take some time. Please be patient. 
Please confirm you still wish to proceed to {1} the column", warningReason, op);
			DialogResult dr = System.Windows.Forms.MessageBox.Show(this, warning, op, System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Question);
			return (dr) == System.Windows.Forms.DialogResult.No ? true : false;
		}

		private void SetFieldCombo(RadioButton radio, int selection)
		{
			comboSqlType.Items.Clear();
			List<String> types = this.typeMap[radio];
			foreach (String type in types)
				comboSqlType.Items.Add(type);
			comboSqlType.SelectedIndex = selection;
		}

		private RadioButton SetRadioSelectionFromFieldInfo(out int selection)
		{
			// new field make varchar
			if (this.fieldInfo.SqlType.Length == 0)
			{
				selection = 0;
				return radioString;
			}

			RadioButton rb = radioString;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioNumber;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioDate;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			if (selection != -1) return rb;
			rb = radioOther;
			selection = SelectionIndexOf(this.fieldInfo.SqlType, rb);
			return rb;
		}

		private int SelectionIndexOf(string p, RadioButton radio)
		{
			List<String> types = this.typeMap[radio];
			for (int idx = 0; idx < types.Count; ++idx)
			{
				if (types[idx].ToLower() == p.ToLower()) return idx;
			}
			return -1;
		}
		
		private void InitialiseTypeCategories()
		{
			this.typeMap.Add(radioString, new List<String>());
			PopulateList(this.typeMap[radioString], "nvarchar,nchar,ntext"); // ntext for > 4000 
			
			this.typeMap.Add(radioNumber, new List<String>());
			PopulateList(this.typeMap[radioNumber], "int,"+SQL_NUMERIC_TYPE+",float,bigint,smallint,tinyint,money,"+SQL_DECIMAL_TYPE+",real");

			this.typeMap.Add(radioDate, new List<String>());
			PopulateList(this.typeMap[radioDate], "datetime,date");

			this.typeMap.Add(radioOther, new List<String>());
			PopulateList(this.typeMap[radioOther], "bit,uniqueidentifier,binary,varbinary,image");
		}

		private void PopulateList(List<string> list,string csv)
		{
			String[] items = csv.Split(',');
			foreach (String item in items)
				list.Add(item);
		}

		// detect when to use the length entry as the Dp entry. 
		private bool LengthIsDp(String sqlType)
		{
			// may be needed with money one day.
			return (sqlType == SQL_DECIMAL_TYPE || sqlType == SQL_NUMERIC_TYPE) ? true : false;
		}

		const int INVALID_FIELD_LENGTH = -9;
		// navigating between ease and utility, below defines some defaults for a decimal type
		// length 15 is good for I think 999,000,000,000,000. 
		const String SQL_DECIMAL_TYPE = "decimal";
		const String SQL_NUMERIC_TYPE = "numeric";
		const int DEFAULT_DECIMAL_LENGTH = 15;

		//
		String tableName;
		SqlTableSchema schema;
		DbView.FieldInfo fieldInfo;
		DbView.FieldInfo fieldInfoOriginal;
		// 
		Dictionary<RadioButton, List<String>> typeMap = new Dictionary<RadioButton,List<string>>();

// 0 = table
// 1 = new field
// 2 = new type
// 3 old field
		const String ALT_MODIFY_COPY_DATA = @"
alter table {0} add column {1} {2};
update {0} set {1} = convert({2}, {3});
alter table {0} drop column {3};
";

// 0 = table
// 1 = old field
// 2 = new type
// 3 = Add script
// 4 = Drop constraint
// 5 = pk field list
// 6 = pk field match h.F1 = tgt.F1
		const String ALT_MODIFY = @"
-- backup column 
if object_id('tempdb..#holddata') is not null drop table #holddata

select {5}, convert({2}, {1}) as {1}
  into #holddata
  from {0}

-- remove old add new
{4}
alter table {0} drop column [{1}]
{3} -- alter table {0} add [{1}] {2}

-- refresh data
update tgt set {1} = h.{1}
from {0} tgt
join #holddata h on {6}
";
// 0 = table
// 1 = old field
// 2 = new type
// 3 = Add script
// 4 = Drop constraint
		const String ALT_MODIFY_NOKEEPDATA = @"
-- remove old add new
{4}
alter table {0} drop column [{1}]
{3} -- alter table {0} add [{1}] {2}
";

// 0 = table
// 1 = old field
// 2 = new field
// 3 = new type
// 4 = pk field list
		const String ALT_MODIFY_HOLDDATA = @"
-- backup column 
if object_id('tempdb..#holddata') is not null drop table #holddata

select {4}, convert({3}, {1}) as {2}
  into #holddata
  from {0}
";
// 0 = table
// 1 = old field
// 2 = new field
// 3 = new type
// 4 = new length
// 5 = pk field list
		const String ALT_MODIFY_HOLDDATA_RESIZE = @"
-- backup column 
if object_id('tempdb..#holddata') is not null drop table #holddata

select {5}, convert({3}({4}), Left({1},{4})) as {2}
  into #holddata
  from {0}
";

		const String ALT_MODIFY_RESTORE_DATA = @"
-- refresh data
update tgt set {1} = h.{1}
from {0} tgt
join #holddata h on {2}
";
	}
}
